import sqlite3


def create_database():
    """
    创建数据库和books表（如果它们不存在）。
    """
    conn = sqlite3.connect('library.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            year_published INT NOT NULL
        )
    ''')
    conn.commit()
    conn.close()


create_database()


class Book:
    """
    表示一本书，包含书名、作者、出版年份和ID。
    """

    def __init__(self, title, author, year_published, id=None):
        self.title = title
        self.author = author
        self.year_published = year_published
        self.id = id

    def to_dict(self):
        """
        将书本对象转换为字典。

        :return: 包含书本属性的字典
        """
        return {
            'id': self.id,
            'title': self.title,
            'author': self.author,
            'year_published': self.year_published
        }


class Library:
    """
    ���理图书数据库，提供添加、删除和查找图书的功能。
    """

    @staticmethod
    def add_book(book):
        """
        向数据库中添加一本新书。

        :param book: 要添加的Book对象
        """
        conn = sqlite3.connect('library.db')
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO books (title, author, year_published)
            VALUES (?, ?, ?)
        ''', (book.title, book.author, book.year_published))
        book.id = cursor.lastrowid
        conn.commit()
        conn.close()

    @staticmethod
    def delete_book(book_id):
        """
        从数据库中删除指定ID的图书。

        :param book_id: 要删除的图书的ID
        """
        conn = sqlite3.connect('library.db')
        cursor = conn.cursor()
        cursor.execute('''
            DELETE FROM books WHERE id = ?
        ''', (book_id,))
        conn.commit()
        conn.close()

    @staticmethod
    def find_book_by_title(title):
        """
        根据书名查找图书。

        :param title: 要查找的图书的书名
        :return: 找到的Book对象，如果没有找到则返回None
        """
        conn = sqlite3.connect('library.db')
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM books WHERE title = ?
        ''', (title,))
        book = cursor.fetchone()
        if book:
            return Book(book[1], book[2], book[3], id=book[0])
        conn.close()
        return None

    @staticmethod
    def list_books():
        """
        列出数据库中的所有图书。

        :return: 包含所有Book对象的列表
        """
        conn = sqlite3.connect('library.db')
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM books
        ''')
        books = cursor.fetchall()
        book_list = []
        for book in books:
            book_list.append(Book(book[1], book[2], book[3], id=book[0]))
        conn.close()
        return book_list

    @staticmethod
    def list_books_by_author(author):
        """
        根据作者列出图书。

        :param author: 要查找的作者
        :return: 包含所有对应Author的Book对象的列表
        """
        conn = sqlite3.connect('library.db')
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM books WHERE author = ?
        ''', (author,))
        books = cursor.fetchall()
        book_list = []
        for book in books:
            book_list.append(Book(book[1], book[2], book[3], id=book[0]))
        conn.close()
        return book_list
    

def main():
    """
    主函数，演示Library类的功能。
    """
    library = Library()

    # 添加图书
    book1 = Book("大卫·科波菲尔", "狄更斯", 1850)
    library.add_book(book1)
    book2 = Book("百年孤独", "加西亚·马尔克斯", 1967)
    library.add_book(book2)
    book3 = Book("活着", "余华", 1992)
    library.add_book(book3)

    # 列出所有图书
    print("\n所有书目:")
    for book in library.list_books():
        print(f"书名: 《{book.title}》, 作者: {book.author}, 出版年份: {book.year_published}, 索书号: {book.id}")

    # 查找图书
    found_book = library.find_book_by_title("百年孤独")
    if found_book:
        print(
            f"\n查找书目: 书名 - 《{found_book.title}》, 作者 - {found_book.author}, 出版年份 - {found_book.year_published}")
    else:
        print("\n暂未收录")

    # 删除图书
    library.delete_book(book1.id)

    # 列出所有图书，验证删除效果
    print("\n删除 '大卫·科波菲尔' 后的所有书目:")
    for book in library.list_books():
        print(f"书名: 《{book.title}》, 作者: {book.author}, 出版年份: {book.year_published}, 索书号: {book.id}")


if __name__ == "__main__":
    main()
    
